package com.carlinfo.test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * jdbc�������ݿ�
 * 
 * @author 90711
 *
 */
public class jdbcMain {
	public static void main(String[] args) {
		System.out.println("=====");
		new jdbcMain().insert("天亮", "女");
//		new jdbcMain().delete(1);
//		new jdbcMain().select();
		
	}
	
	/**
	 * ��������
	 * @param name
	 */
	public void insert(String name,String sex) {
		String driver = "com.mysql.jdbc.Driver";
		String url = "jdbc:mysql://localhost:3306/student?useSSL=false&user=root&password=123456";
		
		String sql = "insert into table_1(sex,name) values('"+sex+"','"+name+"');";
		
		
		Connection conc = null;
		Statement stat = null;
		try {
			Class.forName(driver);
			conc = DriverManager.getConnection(url);
			stat = conc.createStatement();
			System.out.println("===sql==="+sql);
			int res = stat.executeUpdate(sql);
			System.out.println("===存储成功ֵ==="+res);
			
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			try {
				if(stat!=null) {
					stat.close();
					stat = null;
				}
				
			}catch(SQLException e)
			{
				e.printStackTrace();
			}
			try {
				if(conc!=null) {
					conc.close();
					conc = null;
				}
				
			}catch(SQLException e)
			{
				e.printStackTrace();
			}
		}
		
	}
	/**
	 * ��ѯ����
	 */
	public void select(){
		String driver = "com.mysql.jdbc.Driver";
		String url = "jdbc:mysql://127.0.0.1:3306/student?useSSL=false";
		String user = "root";
		String name1 = "3_陈欣_3";
		
		/* �޸�sqlע���bug */
//		String sex1 = "'or 1=1 or sex = '";
		String sex1 = "男";
		String password = "123456";
		
		/* sql��� */
		String sql = "select * from table_1 where sex = ? and name = ? ;";
		Connection conc = null;
		PreparedStatement ps = null;
		Statement stat = null;
		ResultSet ret = null;

		try {
			// Driver driver1 = new Driver();
			Class.forName(driver);
			/* ��ȡ���� */
			conc = DriverManager.getConnection(url, user, password);
			ps = conc.prepareStatement(sql);
			ps.setString(2, name1);
			ps.setString(1,sex1);
			ret = ps.executeQuery();

			if(ret.next()) {
				int id = ret.getInt("id");
				String sex = ret.getString("sex");
				String name = ret.getString("name");
				System.out.println("==��½�ɹ�!--> "+ id + "\t" + sex + "\t" + name);
			}
			else{
				System.out.println("==��¼ʧ���ˣ�==");
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			System.out.println("��������ʧ�ܣ�");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			try {
				if(stat!=null) {
					stat.close();
					stat = null;
				}
				
			}catch(SQLException e)
			{
				e.printStackTrace();
			}
			try {
				if(conc!=null) {
					conc.close();
					conc = null;
				}
				
			}catch(SQLException e)
			{
				e.printStackTrace();
			}
		}
	}
	
	/**
	 * ɾ������
	 * @param id
	 */
	public void delete(int id) {
		
		String driver = "com.mysql.jdbc.Driver";
		String url = "jdbc:mysql://localhost:3306/student?useSSL=false&user=root&password=123456";
		String sql = "delete from table_1 where id ="+id;
		Connection conc = null;
		Statement stat = null;
		try {
			Class.forName(driver);
			conc = DriverManager.getConnection(url);
			stat = conc.createStatement();
			System.out.println("===sql==="+sql);
			int res = stat.executeUpdate(sql);
			System.out.println("===����ֵ==="+res);
			
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			try {
				if(stat!=null) {
					stat.close();
					stat = null;
				}
				
			}catch(SQLException e)
			{
				e.printStackTrace();
			}
			try {
				if(conc!=null) {
					conc.close();
					conc = null;
				}
				
			}catch(SQLException e)
			{
				e.printStackTrace();
			}
		}
		
	}
}
